﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Pixysoft.Framework.XSchEmA.Entity;
using System.Data;

namespace Pixysoft.Framework.XSchEmA.Database
{
    class SqlServerSchemaDownloader
    {
        List<string> systblist = new List<string>();

        private static volatile SqlServerSchemaDownloader instance;

        private static object syncRoot = new Object();

        public static SqlServerSchemaDownloader Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (syncRoot)
                    {
                        instance = new SqlServerSchemaDownloader();
                    }
                }

                return instance;

            }
        }

        public SqlServerSchemaDownloader()
        {
            systblist.Add("DTPROPERTIES");
            systblist.Add("SYSCONSTRAINTS");
            systblist.Add("SYSSEGMENTS");
        }

        public void GetTables(string databasename, SqlConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetSchema("Tables", new string[] { databasename });

                foreach (DataRow row in table.Rows)
                {
                    string tabletype = row["TABLE_TYPE"].ToString().Trim().ToUpper();

                    string tablename = row["TABLE_NAME"].ToString().ToUpper().Trim();

                    if (tabletype == "VIEW")
                        continue;

                    if (systblist.Contains(tablename))
                        continue;

                    TableSchema tableSchema = new TableSchema(tablename);
                    schema.Tables.Add(tableSchema);
                }
            }
        }

        public void GetColumns(string databasename, SqlConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetSchema("Columns", new string[] { databasename });

                foreach (TableSchema tbschema in schema.Tables)
                {
                    foreach (DataRow row in Pixysoft.DataTableCoder.Instance.GetRows(table, "TABLE_NAME", tbschema.TableName))
                    {
                        string typename = row["DATA_TYPE"].ToString().Trim().ToUpper();

                        string colname = row["COLUMN_NAME"].ToString().Trim().ToUpper();

                        string defaultValue = row["COLUMN_DEFAULT"].ToString().Trim().ToUpper();


                        ColumnSchema columnSchema = new ColumnSchema();
                        columnSchema.TableName = tbschema.TableName;
                        columnSchema.ColumnName = colname;
                        columnSchema.IsNullable = row["IS_NULLABLE"].ToString().Trim() == "YES" ? true : false;
                        columnSchema.DefaultValue = defaultValue == "" ? null : defaultValue;


                        DataTypeSchema typeschema = new DataTypeSchema(typename,
                          row["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim() != "" ? Int64.Parse(row["CHARACTER_MAXIMUM_LENGTH"].ToString().Trim()) : -1,
                          row["NUMERIC_PRECISION"].ToString().Trim() != "" ? int.Parse(row["NUMERIC_PRECISION"].ToString().Trim()) : -1,
                          row["NUMERIC_SCALE"].ToString().Trim() != "" ? int.Parse(row["NUMERIC_SCALE"].ToString().Trim()) : -1,
                          false);

                        switch (Pixysoft.EnumCoder.ToEnum<SqlServerDataTypeCollection>(typename))
                        {
                            case SqlServerDataTypeCollection.INT:
                            case SqlServerDataTypeCollection.SMALLINT:
                            case SqlServerDataTypeCollection.TINYINT:
                            case SqlServerDataTypeCollection.BIT:
                            case SqlServerDataTypeCollection.BIGINT:
                                {
                                    typeschema.Length = -1;
                                    typeschema.Scale = -1;
                                    break;
                                }
                            case SqlServerDataTypeCollection.NUMERIC:
                            case SqlServerDataTypeCollection.DECIMAL:
                            case SqlServerDataTypeCollection.FLOAT:
                            case SqlServerDataTypeCollection.REAL:
                                {
                                    typeschema.Length = -1;
                                    break;
                                }



                            case SqlServerDataTypeCollection.UNIQUEIDENTIFIER:
                            case SqlServerDataTypeCollection.SQL_VARIANT:
                            case SqlServerDataTypeCollection.SMALLDATETIME:
                            case SqlServerDataTypeCollection.TIMESTAMP:
                            case SqlServerDataTypeCollection.DATETIME:
                                {
                                    typeschema.Length = -1;
                                    typeschema.Scale = -1;
                                    typeschema.Precision = -1;
                                    break;
                                }

                            case SqlServerDataTypeCollection.VARBINARY:
                            case SqlServerDataTypeCollection.VARCHAR:
                            case SqlServerDataTypeCollection.TEXT:
                            case SqlServerDataTypeCollection.NCHAR:
                            case SqlServerDataTypeCollection.NVARCHAR:
                            case SqlServerDataTypeCollection.NTEXT:
                            case SqlServerDataTypeCollection.MONEY:
                            case SqlServerDataTypeCollection.SMALLMONEY:
                            case SqlServerDataTypeCollection.IMAGE:
                            case SqlServerDataTypeCollection.CHAR:
                            case SqlServerDataTypeCollection.BINARY:
                                {
                                    break;
                                }
                            //case SqlServerDataTypeCollection.CURSOR:

                            default:
                                break;
                        }

                        columnSchema.DataType = typeschema;

                        tbschema.Columns.Add(columnSchema);
                    }
                }

            }
        }

        public void GetIdentifyColumns(string databasename, SqlConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.Parameters.Clear();
                command.CommandText = "SELECT TABLE_NAME,COLUMN_NAME,(columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')) AS [IDENT] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = '" + databasename + "'";

                SqlDataAdapter adapter = new SqlDataAdapter(command);

                DataTable table = new DataTable();
                adapter.Fill(table);

                //填充字段注释

                foreach (DataRow row in table.Rows)
                {
                    string tablename = row["TABLE_NAME"].ToString().Trim().ToUpper();

                    string columnname = row["COLUMN_NAME"].ToString().Trim().ToUpper();

                    string isidentity = row["IDENT"].ToString();

                    if (isidentity.Trim().Equals("0", StringComparison.OrdinalIgnoreCase))
                        continue;

                    TableSchema tbschema = schema.GetTable(tablename);

                    if (tbschema == null)
                    {
                        LoggerHelper.Error("cannot not found tablename = " + tablename);

                        continue;
                    }

                    ColumnSchema colschema = tbschema.GetColumn(columnname);

                    if (colschema == null)
                    {
                        LoggerHelper.Error("cannot not found columnname in table, column= {0}, table = {1}", columnname, tablename);

                        continue;
                    }

                    colschema.DataType = new DataTypeSchema(colschema.DataType.DataType,
                        colschema.DataType.Length, colschema.DataType.Precision, colschema.DataType.Scale, true);
                }
            }
        }

        internal void GetConstraints(SqlConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.Parameters.Clear();
                command.CommandText = "select t.name as TABLENAME, c.name as CONSTNAME, c.xtype as XTYPE,i.indid as INDEXID, ikey.colid as COLID,ikey.keyno as KEYNO, col.name as COLNAME from ( ( ( ( sysobjects as c inner join sysobjects as t on c.parent_obj = t.id and t.xtype = 'U' and (c.xtype = 'PK' or c.xtype = 'UQ')) inner join sysindexes as i on c.name = i.name) inner join sysindexkeys as ikey on i.id = ikey.id and i.indid = ikey.indid ) inner join syscolumns as col on ikey.colid = col.colid and ikey.id = col.id)";
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable table = new DataTable();
                try
                {
                    adapter.Fill(table);
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error("sqlserver.downloadschema.getconstraint.");

                    LoggerHelper.Error(ex.ToString());

                    return;
                }

                foreach (TableSchema tbschema in schema.Tables)
                {
                    DataRow[] rows = Pixysoft.DataTableCoder.Instance.GetRows(table, "TABLENAME", tbschema.TableName);

                    foreach (DataRow row in rows)
                    {
                        string xtype = row["XTYPE"].ToString().Trim().ToUpper();
                        string tablename = row["TABLENAME"].ToString().Trim().ToUpper();
                        string constname = row["CONSTNAME"].ToString().Trim().ToUpper();
                        string colname = row["COLNAME"].ToString().Trim().ToUpper();
                        int colindex = int.Parse(row["KEYNO"].ToString()) - 1;

                        if (xtype == "PK")
                        {
                            PrimaryKeySchema pkSchema = schema.GetPrimaryKeyByName(tablename, constname);

                            if (pkSchema == null)
                            {
                                pkSchema = new PrimaryKeySchema();
                                schema.PrimaryKeys.Add(pkSchema);
                            }
                            pkSchema.ConsName = constname;
                            pkSchema.IndexName = constname;
                            pkSchema.TableName = tablename;
                            SchemaUtility.AddToList<string>(pkSchema.ColumnNames, colname, colindex);

                        }
                        else if (xtype == "UQ")
                        {
                            UniqueSchema ukSchema = schema.GetUniqueByName(constname);

                            if (ukSchema == null)
                            {
                                ukSchema = new UniqueSchema();
                                schema.Uniques.Add(ukSchema);
                            }

                            ukSchema.ConsName = constname;
                            ukSchema.IndexName = constname;
                            ukSchema.TableName = tablename;
                            SchemaUtility.AddToList<string>(ukSchema.ColumnNames, colname, colindex);

                        }
                    }
                }
            }
        }

        internal void GetIndexes(SqlConnection conn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.Parameters.Clear();
                command.CommandText = "select c.name as TABLENAME, i.id as TABLEID, i.indid as INDEXID, i.name as INDEXNAME, ikey.colid as COLID, ikey.keyno as KEYNO, col.name as COLNAME from ( ( ( sysindexes as i inner join sysobjects as c on i.id = c.id and c.xtype = 'U') inner join sysindexkeys as ikey on i.id = ikey.id and i.indid = ikey.indid ) inner join syscolumns as col on ikey.id = col.id and ikey.colid = col.colid )";
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable table = new DataTable();
                try
                {
                    adapter.Fill(table);
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error("sqlserver.downloadschema.getindexes.");

                    LoggerHelper.Error(ex.ToString());

                    return;
                }

                foreach (DataRow row in table.Rows)
                {
                    string tablename = row["TABLENAME"].ToString().Trim().ToUpper();
                    string constname = row["INDEXNAME"].ToString().Trim().ToUpper();
                    int colindex = int.Parse(row["KEYNO"].ToString()) - 1;
                    string colname = row["COLNAME"].ToString().Trim().ToUpper();

                    if (schema.ContainedPrimaryKey(constname))
                        continue;

                    if (schema.ContainedUnique(constname))
                        continue;

                    IndexSchema ischema = schema.GetIndexByName(constname);

                    if (ischema == null)
                    {
                        ischema = new IndexSchema();
                        schema.Indexs.Add(ischema);
                    }
                    ischema.TableName = tablename;
                    ischema.IndexName = constname;
                    ischema.ConsName = constname;
                    SchemaUtility.AddToList<string>(ischema.ColumnNames, colname, colindex);
                }
            }
        }

        internal void GetForeignKeys(SqlConnection oconn, DatabaseSchema schema)
        {
            lock (syncRoot)
            {
                if (((oconn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    oconn.Open();
                }

                SqlCommand command = new SqlCommand();
                command.Connection = oconn;
                command.Parameters.Clear();
                command.CommandText = "select t.name as tablename, c.name as constname,ifkey.fkeyid as fkeyid,ifkey.rkeyid as rkeyid, ifkey.keyno as keyno, fkey.name as ftablename, fkeycol.name as fcolname, rkey.name as rtablename, rkeycol.name as rcolname from ( ( ( ( ( ( sysobjects as c inner join sysobjects as t on c.parent_obj = t.id and c.xtype = 'F' ) inner join sysforeignkeys as ifkey on c.id = ifkey.constid ) inner join sysobjects as fkey on ifkey.fkeyid = fkey.id ) inner join syscolumns as fkeycol on ifkey.fkeyid = fkeycol.id and ifkey.fkey = fkeycol.colid) inner join sysobjects as rkey on ifkey.rkeyid = rkey.id ) inner join syscolumns as rkeycol on ifkey.rkeyid = rkeycol.id and ifkey.rkey = rkeycol.colid)";
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable table = new DataTable();
                try
                {
                    adapter.Fill(table);
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error("sqlserver.downloadschema.getforeignkeys.");

                    LoggerHelper.Error(ex.ToString());

                    return;
                }

                List<string> rcollist = new List<string>();
                string rtable = null;
                ForeignKeySchema formerschema = null;

                foreach (DataRow row in table.Rows)
                {
                    string constname = row["CONSTNAME"].ToString().Trim().ToUpper();
                    string ftablename = row["FTABLENAME"].ToString().Trim().ToUpper();
                    string rtablename = row["RTABLENAME"].ToString().Trim().ToUpper();
                    string fcolname = row["FCOLNAME"].ToString().Trim().ToUpper();
                    string rcolname = row["RCOLNAME"].ToString().Trim().ToUpper();
                    int colindex = int.Parse(row["KEYNO"].ToString()) - 1;

                    ForeignKeySchema fkschema = schema.GetForeignKeyByName(constname);

                    if (fkschema == null)
                    {
                        if (rcollist.Count > 0 && formerschema != null)
                        {
                            IConstraintSchema constschema = schema.GetConstraint(rtable, rcollist);

                            if (constschema != null)
                            {
                                string pkconstname = constschema.ConsName;
                                formerschema.PkeyConsName = pkconstname;

                                //2010-08-01 支持最新的fk结构

                                formerschema.PkTableName = constschema.TableName;
                                foreach (string column in constschema.ColumnNames)
                                    formerschema.PkColumnNames.Add(column);
                            }
                        }

                        fkschema = new ForeignKeySchema();
                        schema.ForeignKeys.Add(fkschema);
                        rcollist = new List<string>();
                        rtable = null;
                        formerschema = null;
                    }

                    fkschema.ConsName = constname;
                    fkschema.FkTableName = ftablename;
                    SchemaUtility.AddToList<string>(fkschema.FkColumnNames, fcolname, colindex);
                    SchemaUtility.AddToList<string>(rcollist, rcolname, colindex);
                    rtable = rtablename;
                    formerschema = fkschema;
                }

                if (rcollist.Count > 0 && formerschema != null)
                {
                    IConstraintSchema constschema = schema.GetConstraint(rtable, rcollist);

                    if (constschema != null)
                    {
                        string pkconstname = constschema.ConsName;
                        formerschema.PkeyConsName = pkconstname;


                        //2010-08-01 支持最新的fk结构

                        formerschema.PkTableName = constschema.TableName;
                        foreach (string column in constschema.ColumnNames)
                            formerschema.PkColumnNames.Add(column);
                    }
                }

                if (oconn.State == ConnectionState.Open)
                    oconn.Close();
            }
        }
    }
}
